package com.cros.mysql;

import java.sql.*;

public class Conexion{
	

  private String driver="com.mysql.jdbc.Driver";
  private String url="jdbc:mysql://192.168.0.130:3306/promocionesweb";
  private String user="root";
  private String password="adapter";
  
  private Connection conn = null;
  private Statement stmt 	= null;
  private PreparedStatement pstmt = null;
  private ResultSet resultSet = null;

  public Conexion(){
	  
  }
  
  public Connection getConexion(){
	    try{
	    	
	        Class.forName(driver).newInstance();
	        conn = DriverManager.getConnection(url,user,password);
	        conn.setAutoCommit(false); 
	        
	      }catch (ClassNotFoundException ex) {
	      	System.err.println(ex.getMessage());
	      }catch (IllegalAccessException ex) {
	      	System.err.println(ex.getMessage());
	      }catch (InstantiationException ex) {
	      	System.err.println(ex.getMessage());
	      }catch (SQLException ex) {
	      	System.err.println(ex.getMessage());
	      	}
	    return conn;
  }
  
  
  public void closeConexion(){
	  try {
		if(resultSet != null){
			resultSet.close();
		 }
		if(pstmt !=null){
			pstmt.close();
		}
		if(stmt != null){
			stmt.close();
		}
		if(conn != null){
			conn.close();
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}finally{
		resultSet=null;
		pstmt=null;
		stmt=null;
		conn=null;
	}
  }
  
  public int insert(String sqlInsert) throws SQLException{
	  try { 
		pstmt = conn.prepareStatement(sqlInsert,PreparedStatement.RETURN_GENERATED_KEYS);
      	pstmt.executeUpdate();
      	resultSet 	= pstmt.getGeneratedKeys();  
		int res = -1;
		if(resultSet.next()){
      		res = resultSet.getInt(1);
      		System.out.println("Clave generada = " + res);
		}
		return res;
	} catch (SQLException e) {
		e.printStackTrace();
		throw e;
	}finally{
		try{
			if(resultSet != null){
				resultSet.close();
			 }
		}catch(Exception ex){
			ex.printStackTrace();
		}
		try{
			if(pstmt !=null){
				pstmt.close();
		}
		}catch(Exception ex){
		ex.printStackTrace();
		}
		pstmt=null;
		resultSet=null;
	}
	  
  }
  
  
  public boolean serealizoelinsert(String sqlInsert) throws SQLException{
	  try { 
		pstmt = conn.prepareStatement(sqlInsert,PreparedStatement.RETURN_GENERATED_KEYS);
		int valor=pstmt.executeUpdate();
		if(valor==0){
			return false;
		}else{
			return true;
		}
	} catch (SQLException e) {
		e.printStackTrace();
		throw e;
	}finally{
		try{
			if(resultSet != null){
				resultSet.close();
			 }
		}catch(Exception ex){
			ex.printStackTrace();
		}
		try{
			if(pstmt !=null){
				pstmt.close();
		}
		}catch(Exception ex){
		ex.printStackTrace();
		}
		pstmt=null;
		resultSet=null;
	}
	  
  }
  
  public int select(String sqlselect) throws SQLException{
	  try {
		stmt =conn.createStatement();
		resultSet =stmt.executeQuery(sqlselect);

		int res = -1;
		if(resultSet.next()){
      		res = resultSet.getInt(1);
      		System.out.println("Clave generada = " + res);
      	}
		return res;
	} catch (SQLException e) {
		e.printStackTrace();
		throw e;
	}finally{
		try{
			if(resultSet != null){
				resultSet.close();
			 }
		}catch(Exception ex){
			ex.printStackTrace();
		}
		try{
			if(stmt !=null){
				stmt.close();
		}
		}catch(Exception ex){
		ex.printStackTrace();
		}
		stmt=null;
		resultSet=null;
	}
	  
  }
  
  public void commit() throws SQLException{
      try {
		conn.commit();
		System.out.println("Transaction commit...");
	} catch (SQLException e) {
		e.printStackTrace();
	}
     
  }
  
  public void rollBack() throws SQLException{
      try {
    	  if(conn!=null){
 			 conn.rollback();
 		     System.out.println("Connection rollback...");
 		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
     
  }
  
  


}